# coding=utf-8
import pymysql
from sshtunnel import SSHTunnelForwarder
from common.caseLog import Log

class Connect_Mysql():
    def __init__(self, host, user, password, db_name):
        self.log = Log()
        # if ssh_host != None:
        #     with SSHTunnelForwarder(
        #             (ssh_host, 22),  # B机器的配置
        #             ssh_password="sshpasswd",
        #             ssh_username="sshusername",
        #             remote_bind_address=(ssh_mysql, 3306)) as server:  # A机器的配置
        #         try:
        #             self.db = pymysql.connect(host='127.0.0.1',  # 此处必须是是127.0.0.1
        #                                       port=server.local_bind_port,
        #                                       user=user,
        #                                       passwd=password,
        #                                       db=db_name,
        #                                       cursorclass=pymysql.cursors.DictCursor
        #                                       )
        #         except Exception as e:
        #             self.log.error(e)
        # else:
        try:
            self.db = pymysql.connect(host=host,
                                      user=user,
                                      password=password,
                                      db=db_name,
                                      charset='utf8mb4',
                                      cursorclass=pymysql.cursors.DictCursor
                                      )
        except pymysql.Error as e:
            self.log.error(e)

    def commit_table(self, sql):
        # 提交
        try:
            cursors = self.db.cursor()
            cursors.execute(sql)
            self.db.commit()
        except pymysql.Error as e:
            self.log.error(e)
        finally:
            self.db.close()


    def select_table(self, sql):
        # 查询
        try:
            cursors = self.db.cursor()
            cursors.execute(sql)
            result = cursors.fetchall()
            return result
        except pymysql.Error as e:
            self.log.error(e)
        finally:
            self.db.close()